package dao;

import entity.MaterialBean;
import util.DBUtil;
import myUtil.materialCheck;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MaterialDao {
    /**
     * 获取总页数
     *
     * @param itemId
     * @param itemName
     * @param itemClasses
     * @param vendorName
     * @param site
     * @param inboundTime
     * @param contact
     * @param phoneNumber
     * @param itemDescription
     * @return
     */
    public int findPageCount(String itemId, String itemName, ArrayList<String> itemClasses, String vendorName, String site, String inboundTime, String contact, String phoneNumber, String itemDescription) {
        String countSql = "select count(*) from material where 1=1 ";
        String sql = "";
        if (!itemId.isEmpty()) {
            sql += " AND itemId LIKE '%" + itemId + "%'";
        }
        if (!itemName.isEmpty()) {
            sql += " AND itemName = '" + itemName + "'";
        }
        if (!itemClasses.isEmpty()) {
            if(itemClasses.size()==1&&itemClasses.get(0).equals("")){
            }else {
                sql += " AND itemClasses in " + "(";
                for (int i = 0; i < itemClasses.size(); i++) {
                    sql += "'" + itemClasses.get(i) + "'";
                    if (i != itemClasses.size() - 1)
                        sql += ",";
                }
                sql += ")";
            }
        }
        if (!vendorName.isEmpty()) {
            sql += " AND vendorName = '" + vendorName + "'";
        }
        if (!phoneNumber.isEmpty()) {
            sql += " AND phoneNumber = '" + phoneNumber + "'";
        }
        countSql += sql;

        int totalCount = 0;
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            pstmt = connection.prepareStatement(countSql);
            resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                totalCount = resultSet.getInt(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (resultSet != null)
                    resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                if (pstmt != null)
                    pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            if (connection != null)
                DBUtil.freeConnection(connection);
        }
        int page_size = 6;
        return ((totalCount + page_size) - 1) / page_size;
    }

    /**
     * @param itemId
     * @param itemName
     * @param itemClasses
     * @param vendorName
     * @param site
     * @param inboundTime
     * @param contact
     * @param phoneNumber
     * @param itemDescription
     * @param curpage
     * @return
     */
    public ArrayList<MaterialBean> findMaterial(String itemId, String itemName, ArrayList<String> itemClasses, String vendorName, String site, String inboundTime, String contact, String phoneNumber, String itemDescription, int curpage) {
        ArrayList<MaterialBean> materialBeanList = new ArrayList<>();
        String sql = "SELECT * FROM material WHERE 1=1 ";
        if (!itemId.isEmpty()) {
            sql += " AND itemId LIKE '%" + itemId + "%'";
        }
        if (!itemName.isEmpty()) {
            sql += " AND itemName = '" + itemName + "'";
        }
        if (!itemClasses.isEmpty()) {
            if(itemClasses.size()==1&&itemClasses.get(0).equals("")){
            }else {
                sql += " AND itemClasses in " + "(";
                for (int i = 0; i < itemClasses.size(); i++) {
                    sql += "'" + itemClasses.get(i) + "'";
                    if (i != itemClasses.size() - 1)
                        sql += ",";
                }
                sql += ")";
            }
        }
        if (!vendorName.isEmpty()) {
            sql += " AND vendorName = '" + vendorName + "'";
        }
        if (!phoneNumber.isEmpty()) {
            sql += " AND phoneNumber = '" + phoneNumber + "'";
        }
        int page_size = 6;
        int start = curpage * page_size - page_size;
        String pge = " LIMIT " + start + ", " + page_size;  // 注意前面添加了一个空格,没这个空格就会报错
        sql = sql + pge;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
//        System.out.println(sql);

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            // 访问数据
            while (rs.next()) {
                MaterialBean obj = new MaterialBean();
                obj.setId(rs.getInt("id"));
                obj.setItemId(rs.getString("itemId"));
                obj.setItemName(rs.getString("itemName"));
                obj.setItemClasses(rs.getString("itemClasses"));
                obj.setVendorName(rs.getString("vendorName"));
                obj.setSite(rs.getString("site"));
                obj.setInboundTime(rs.getString("inboundTime"));
                obj.setContact(rs.getString("contact"));
                obj.setPhoneNumber(rs.getString("phoneNumber"));
                obj.setItemDescription(rs.getString("itemDescription"));
                materialBeanList.add(obj);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if(rs!=null)
                    rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                if(stmt!=null)
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return materialBeanList;
    }

    /**
     * @param itemId
     * @param itemName
     * @param itemClasses
     * @param vendorName
     * @param site
     * @param inboundTime
     * @param contact
     * @param phoneNumber
     * @param itemDescription
     * @return
     * @throws SQLException
     */
    public boolean addMaterial(String itemId, String itemName, String itemClasses, String vendorName, String site, String inboundTime, String contact, String phoneNumber, String itemDescription) throws SQLException {
        if (materialCheck.checkNumber(itemId) && materialCheck.checkS(itemName) && materialCheck.checkS(itemClasses) &&
                materialCheck.checkS(vendorName) && materialCheck.checkS(site) && materialCheck.checkS(inboundTime) &&
                materialCheck.checkS(contact) && materialCheck.checkPhoneNumber(phoneNumber)) {
            Connection conn = DBUtil.getConnection();
            String sql = "INSERT INTO material(itemId,itemName,itemClasses,vendorName,site,inboundTime,contact,phoneNumber,itemDescription)  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
            //需要先获取到数据总量(设置id+1)
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, itemId);
            preparedStatement.setString(2, itemName);
            preparedStatement.setString(3, itemClasses);
            preparedStatement.setString(4, vendorName);
            preparedStatement.setString(5, site);
            preparedStatement.setString(6, inboundTime);
            preparedStatement.setString(7, contact);
            preparedStatement.setString(8, phoneNumber);
            preparedStatement.setString(9, itemDescription);
            preparedStatement.executeUpdate();
            System.out.print("hello");

            return true;
        } else
            return false;
    }

    /**
     * @param id
     * @param itemId
     * @param itemName
     * @param itemClasses
     * @param vendorName
     * @param site
     * @param inboundTime
     * @param contact
     * @param phoneNumber
     * @param itemDescription
     * @return
     * @throws SQLException
     */
    public  boolean editMaterial(int id, String itemId, String itemName, String itemClasses, String vendorName, String site, String inboundTime, String contact, String phoneNumber, String itemDescription) throws SQLException {
        if (materialCheck.checkNumber(itemId) && materialCheck.checkS(itemName) && materialCheck.checkS(itemClasses) &&
                materialCheck.checkS(vendorName) && materialCheck.checkS(site) && materialCheck.checkS(inboundTime) &&
                materialCheck.checkS(contact) && materialCheck.checkPhoneNumber(phoneNumber)) {
            Connection conn = DBUtil.getConnection();
            System.out.print("editMaterial:"+id);
            //根据数据id进行数据更新
            String sql = String.format("update material set itemId=?, itemName=?, itemClasses=?, vendorName=?, site=?, inboundTime=?, contact=?, phoneNumber=?, itemDescription=? where id=%d", id);
            PreparedStatement preparedStatement = conn.prepareStatement(sql);

            preparedStatement.setString(1, itemId);
            preparedStatement.setString(2, itemName);
            preparedStatement.setString(3, itemClasses);
            preparedStatement.setString(4, vendorName);
            preparedStatement.setString(5, site);
            preparedStatement.setString(6, inboundTime);
            preparedStatement.setString(7, contact);
            preparedStatement.setString(8, phoneNumber);
            preparedStatement.setString(9, itemDescription);

            preparedStatement.executeUpdate();
            return true;
        } else
            return false;
    }

    public  MaterialBean findMaterialById(int id) throws SQLException {
        Connection conn = DBUtil.getConnection();
        String sql = "select * from material where id=" + id;
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ResultSet rs = preparedStatement.executeQuery();
        rs.next();
        MaterialBean obj = new MaterialBean();
        obj.setId(rs.getInt("id"));
        obj.setItemId(rs.getString("itemId"));
        obj.setItemName(rs.getString("itemName"));
        obj.setItemClasses(rs.getString("itemClasses"));
        obj.setVendorName(rs.getString("vendorName"));
        obj.setSite(rs.getString("site"));
        obj.setInboundTime(rs.getString("inboundTime"));
        obj.setContact(rs.getString("contact"));
        obj.setPhoneNumber(rs.getString("phoneNumber"));
        obj.setItemDescription(rs.getString("itemDescription"));
        return obj;
    }

    public boolean deleteMaterialById(int id) throws SQLException {
        Connection conn = DBUtil.getConnection();
        String sql = "delete from material where id=" + id;
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int res = preparedStatement.executeUpdate();
        if (res > 0) {
            return true;
        } else {
            return false;
        }
    }

    public int getTheLastId() throws SQLException {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        String sql = "select count(*) from material";
        int id=0;
        try {
            connection = DBUtil.getConnection();
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                id = resultSet.getInt(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (resultSet != null)
                    resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                if (pstmt != null)
                    pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            if (connection != null)
                DBUtil.freeConnection(connection);
        }
        return id;
    }
}
